--物料详情报表
alter PROCEDURE getMaterialReport
    --产品编码
    @item_code VARCHAR(100),
    --批量工单号
    @batch_number_list VARCHAR(2000),
    --工单状态
    @production_line VARCHAR(100),
    --物料类型
    @material_type VARCHAR(100),
    @BoStatus NVARCHAR(255),
    @begingmt_create DATETIME,
    @endgmt_create DATETIME,
    @PageIndex INT,
    @PageSize INT,
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算分页的起始位置
    DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
--------------------------------------------------------------------------------------------

    SELECT a.mes_order_create_time                                                                 AS "MES订单创建时间",
           a.production_line                                                                       AS "产线",
           a.team                                                                                  AS "Team",
           mest.name                                                                               AS "Order Type",
           a.wh_org                                                                                AS "WH Org",
           a.customer_code                                                                         AS "客户代码",
           a.customer_name                                                                         AS "客户名称",
           a.batch_number                                                                          AS "工单号",
           a.item_code                                                                             AS "产品名称",
           a.item_category                                                                         AS "产品类别",
           a.item_width                                                                            AS "产品尺寸宽",
           a.item_length                                                                           AS "产品尺寸长",
           a.qty                                                                                   AS "数量",
           a.unit                                                                                  AS "单位",
           a.gross_discharge_qty                                                                   AS "总放数",
           e.name                                                                                  AS "物料类型",
           c.material_code                                                                         AS "物料编码",
--            d.material_desc                                                                       AS "物料描述",
           CASE
               WHEN CONVERT(NVARCHAR(100), e.name) LIKE N'%白料%' THEN CONVERT(NVARCHAR(500), LTRIM(RTRIM(
                       ISNULL(CONVERT(NVARCHAR(100), d.brand_name), '') +
                       CASE
                           WHEN CONVERT(NVARCHAR(100), d.pt) IS NOT NULL AND
                                CONVERT(NVARCHAR(100), d.brand_name) IS NOT NULL
                               THEN ','
                           ELSE ''
                           END +
                       ISNULL(RTRIM(RTRIM(CONVERT(DECIMAL(18, 4), d.pt), '0'), '.') + 'PT', '') +
                       CASE
                           WHEN CONVERT(NVARCHAR(100), d.weight) IS NOT NULL AND
                                (CONVERT(NVARCHAR(100), d.pt) IS NOT NULL OR
                                 CONVERT(NVARCHAR(100), d.brand_name) IS NOT NULL)
                               THEN ','
                           ELSE ''
                           END +
                       ISNULL(RTRIM(RTRIM(CONVERT(DECIMAL(18, 4), d.weight), '0'), '.') + 'GSM', '') +
                       CASE
                           WHEN CONVERT(NVARCHAR(100), d.color) IS NOT NULL AND
                                (CONVERT(NVARCHAR(100), d.weight) IS NOT NULL OR
                                 CONVERT(NVARCHAR(100), d.pt) IS NOT NULL OR
                                 CONVERT(NVARCHAR(100), d.brand_name) IS NOT NULL)
                               THEN ','
                           ELSE ''
                           END +
                       ISNULL(CONVERT(NVARCHAR(100), d.color), ''))))
               ELSE NULL
               END                                                                                 as 物料描述,

           c.unit_usage                                                                            AS "单位用量",
           c.usage_unit                                                                            AS "用量单位",
           c.paper_open                                                                            AS "纸张开数",
           c.open_paper_info                                                                       AS "开纸信息",
           c.material_remark                                                                       AS "物料备注",
           b.process_name                                                                          AS "工序名",
           (select top 1 material_total from mes_bo_process_detail c where c.bo_process_id = b.id) AS "领料合计",
           a.paper_used_num_total                                                                  AS "净需求数",
           a.number_of_layouts                                                                     AS "排版个数",
           a.lot_qty                                                                               AS "LOT个数",
           a.eng                                                                                   AS "ENG",
           msite.name                                                                              AS "工单状态",
           wlzt.name                                                                               AS "物料状态",
           a.current_site                                                                          AS "当前站点",
           j.scan_time                                                                             AS "站点时间",
           a.process_line_desc                                                                     AS "工序汇总",
           a.production_method                                                                     AS "Production Methods",
           a.planner_code                                                                          AS "Planner Code",
           a.typesetting_method                                                                    AS "Typesetting Method",
           a.priority                                                                              AS "Priority",
           a.target_date                                                                           AS "Target Date",
           a.ppc_recurrence                                                                        AS "PPC复期",
           a.standard_shipping_date                                                                AS "标准出货日期",
           a.customer_requires_shipping_date                                                       AS "客户要求出货日期",
           a.billing_officer                                                                       AS "开单人员",
           a.ppc_contact                                                                           AS "PPC负责人",
           a.setup_pe                                                                              AS "Setup PE",
           a.develop_pe                                                                            AS "Develop PE",
           fljl.completed_time                                                                     AS "发料时间"
    FROM mes_batch_order a
             join mes_bo_process b on a.id = b.bo_id
             join mes_bo_process_material c on a.id = c.bo_id and c.bo_process_id = b.id
             left join sys_material d on c.material_id = d.id
             left join sys_material_type e on c.material_type = e.id
             OUTER APPLY (select top 1 *
                          from mes_site_scan j
                          where j.bo_id = a.id
                          order by j.gmt_create desc) j
             left join sys_dict mest on mest.value = a.mes_order_type and mest.type = 'MES_ORDER_TYPE'
             left join sys_dict msite on msite.value = a.status and msite.type = 'ORDER_STATUS'
             left join sys_dict wlzt on wlzt.value = c.material_status and wlzt.type = 'BO_MATERIAL_STATUS'
             outer apply (select top 1 *
                          from mes_mr_issue_record fljl
                          where fljl.bo_process_material_id = c.id
                            and fljl.material_status = 80
                          order by fljl.gmt_create desc) fljl


    WHERE (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@production_line IS NULL OR a.production_line = @production_line)
      AND (@material_type IS NULL OR e.id in (SELECT value FROM STRING_SPLIT(@material_type, ',')))
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@batch_number_list IS NULL OR a.batch_number IN (SELECT value FROM STRING_SPLIT(@batch_number_list, ' ')))
      AND (@item_code IS NULL OR a.item_code = @item_code)
    order by a.gmt_create desc
    ---------------------------------------------------------------------
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
    SELECT @TotalCount = COUNT(1)

    FROM mes_batch_order a
             join mes_bo_process b on a.id = b.bo_id
             join mes_bo_process_material c on a.id = c.bo_id and c.bo_process_id = b.id
             left join sys_material d on c.material_id = d.id
             left join sys_material_type e on c.material_type = e.id
             OUTER APPLY (select top 1 *
                          from mes_site_scan j
                          where j.bo_id = a.id
                          order by j.gmt_create desc) j
             left join sys_dict mest on mest.value = a.mes_order_type and mest.type = 'MES_ORDER_TYPE'
             left join sys_dict msite on msite.value = a.status and msite.type = 'ORDER_STATUS'
             left join sys_dict wlzt on wlzt.value = a.status and wlzt.type = 'BO_MATERIAL_STATUS'
    -------------------------------------------

    WHERE (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@production_line IS NULL OR a.production_line = @production_line)
      AND (@material_type IS NULL OR e.id in (SELECT value FROM STRING_SPLIT(@material_type, ',')))
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@batch_number_list IS NULL OR a.batch_number IN (SELECT value FROM STRING_SPLIT(@batch_number_list, ' ')))
      AND (@item_code IS NULL OR a.item_code = @item_code)

END


